-- ============================================================================
-- 数据库迁移脚本：MinIO 文件存储集成（Phase 3）
-- ============================================================================
-- 创建日期：2025-11-03
-- MinIO 服务器：192.168.1.86:9000
-- 用途：添加文件元数据表，扩展 documents 表以支持 MinIO 文件存储
-- ============================================================================

-- ============================================================================
-- 第一部分：扩展 documents 表
-- ============================================================================

-- 添加 MinIO 相关字段
ALTER TABLE documents 
  ADD COLUMN IF NOT EXISTS file_size BIGINT,
  ADD COLUMN IF NOT EXISTS import_mode VARCHAR(20),
  ADD COLUMN IF NOT EXISTS source_url TEXT,
  ADD COLUMN IF NOT EXISTS imported_at TIMESTAMP WITH TIME ZONE,
  ADD COLUMN IF NOT EXISTS auto_sync BOOLEAN DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS last_synced_at TIMESTAMP WITH TIME ZONE,
  ADD COLUMN IF NOT EXISTS cover_image_id BIGINT,
  ADD COLUMN IF NOT EXISTS cover_image_url TEXT;

-- 添加字段注释
COMMENT ON COLUMN documents.content_type IS '内容类型：direct(数据库)/minio(MinIO)/url(外部)/file(文件系统)/base64';
COMMENT ON COLUMN documents.content_path IS 'MinIO文件路径，格式：bucket/2024/11/uuid.md';
COMMENT ON COLUMN documents.file_size IS '文件大小（字节），用于 minio/url 类型';
COMMENT ON COLUMN documents.import_mode IS '导入模式：null(直接编辑)/reference(引用)/imported(已导入)';
COMMENT ON COLUMN documents.source_url IS '原始来源URL（从外部导入时记录）';
COMMENT ON COLUMN documents.imported_at IS '导入时间';
COMMENT ON COLUMN documents.auto_sync IS '是否自动同步（用于 URL 文档）';
COMMENT ON COLUMN documents.last_synced_at IS '上次同步时间';
COMMENT ON COLUMN documents.cover_image_id IS '关联到 file_metadata 表的封面图片ID';
COMMENT ON COLUMN documents.cover_image_url IS '封面图片URL（冗余字段，方便查询）';

-- 添加新索引
CREATE INDEX IF NOT EXISTS idx_documents_content_type ON documents(content_type);
CREATE INDEX IF NOT EXISTS idx_documents_import_mode ON documents(import_mode);
CREATE INDEX IF NOT EXISTS idx_documents_cover_image_id ON documents(cover_image_id);

-- ============================================================================
-- 第二部分：创建 file_metadata 表
-- ============================================================================

CREATE TABLE IF NOT EXISTS file_metadata (
    id BIGSERIAL PRIMARY KEY,
    
    -- 文件基本信息
    file_name VARCHAR(255) NOT NULL,              -- 原始文件名
    file_path VARCHAR(500) NOT NULL UNIQUE,       -- MinIO 完整路径（bucket/year/month/uuid.ext）
    bucket_name VARCHAR(100) NOT NULL,            -- 所属 Bucket 名称
    object_key VARCHAR(400) NOT NULL,             -- MinIO Object Key（year/month/uuid.ext）
    file_size BIGINT NOT NULL,                    -- 文件大小（字节）
    mime_type VARCHAR(100) NOT NULL,              -- MIME 类型（image/jpeg, application/pdf等）
    file_extension VARCHAR(20),                   -- 文件扩展名（.jpg, .pdf等）
    
    -- 文件分类
    file_type VARCHAR(20) NOT NULL,               -- 文件类型：image/document/markdown/avatar/cover/other
    file_category VARCHAR(50),                    -- 文件分类：doc-image/doc-attachment/user-avatar等
    
    -- 文件标识（用于去重和校验）
    md5_hash VARCHAR(32),                         -- MD5 哈希值
    sha256_hash VARCHAR(64),                      -- SHA256 哈希值（可选，更安全）
    
    -- 图片特有字段
    width INTEGER,                                -- 图片宽度（像素）
    height INTEGER,                               -- 图片高度（像素）
    has_thumbnail BOOLEAN DEFAULT FALSE,          -- 是否有缩略图
    thumbnail_path VARCHAR(500),                  -- 缩略图路径
    thumbnail_url TEXT,                           -- 缩略图访问 URL
    
    -- 访问控制
    access_policy VARCHAR(20) DEFAULT 'public',   -- 访问策略：public/private/authenticated
    is_public BOOLEAN DEFAULT TRUE,               -- 是否公开（冗余字段，方便查询）
    
    -- 使用统计
    download_count INTEGER DEFAULT 0,             -- 下载次数
    view_count INTEGER DEFAULT 0,                 -- 查看次数（图片）
    
    -- 关联信息
    document_id BIGINT,                           -- 关联的文档ID（可为空，未关联的文件）
    uploaded_by UUID,                             -- 上传者用户ID
    
    -- 导入相关（从URL导入时）
    source_url TEXT,                              -- 原始来源URL（从外部下载时）
    imported_from_url BOOLEAN DEFAULT FALSE,      -- 是否从URL导入
    
    -- 元数据
    metadata JSONB DEFAULT '{}'::jsonb,           -- 额外元数据（EXIF、自定义标签等）
    
    -- 软删除
    deleted_at TIMESTAMP WITH TIME ZONE,          -- 软删除时间
    deleted_by UUID,                              -- 删除者ID
    
    -- 时间戳
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    -- 外键约束
    CONSTRAINT fk_file_metadata_document FOREIGN KEY (document_id) 
        REFERENCES documents(id) ON DELETE CASCADE,
    CONSTRAINT fk_file_metadata_uploaded_by FOREIGN KEY (uploaded_by) 
        REFERENCES users(id) ON DELETE SET NULL,
    
    -- 检查约束
    CONSTRAINT chk_file_type CHECK (file_type IN ('image', 'document', 'markdown', 'avatar', 'cover', 'other')),
    CONSTRAINT chk_access_policy CHECK (access_policy IN ('public', 'private', 'authenticated'))
);

-- ============================================================================
-- 第三部分：创建索引
-- ============================================================================

-- 基础索引
CREATE INDEX IF NOT EXISTS idx_file_metadata_document_id ON file_metadata(document_id);
CREATE INDEX IF NOT EXISTS idx_file_metadata_uploaded_by ON file_metadata(uploaded_by);
CREATE INDEX IF NOT EXISTS idx_file_metadata_file_type ON file_metadata(file_type);
CREATE INDEX IF NOT EXISTS idx_file_metadata_bucket_name ON file_metadata(bucket_name);
CREATE INDEX IF NOT EXISTS idx_file_metadata_created_at_desc ON file_metadata(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_file_metadata_md5_hash ON file_metadata(md5_hash);
CREATE INDEX IF NOT EXISTS idx_file_metadata_deleted_at ON file_metadata(deleted_at);

-- 唯一索引（软删除时不重复）
CREATE UNIQUE INDEX IF NOT EXISTS uq_file_metadata_path 
ON file_metadata(file_path) WHERE deleted_at IS NULL;

-- 复合索引（常用查询组合）
CREATE INDEX IF NOT EXISTS idx_file_metadata_doc_type 
ON file_metadata(document_id, file_type) WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_file_metadata_user_type 
ON file_metadata(uploaded_by, file_type) WHERE deleted_at IS NULL;

-- ============================================================================
-- 第四部分：添加表注释
-- ============================================================================

COMMENT ON TABLE file_metadata IS '文件元数据表，存储所有上传到MinIO的文件信息';
COMMENT ON COLUMN file_metadata.file_path IS 'MinIO完整路径，格式：bucket/year/month/uuid.ext，全局唯一';
COMMENT ON COLUMN file_metadata.object_key IS 'MinIO对象键，格式：year/month/uuid.ext';
COMMENT ON COLUMN file_metadata.file_type IS '文件类型：image(图片)/document(附件)/markdown(MD文件)/avatar(头像)/cover(封面)';
COMMENT ON COLUMN file_metadata.md5_hash IS 'MD5哈希值，用于文件去重';
COMMENT ON COLUMN file_metadata.access_policy IS '访问策略：public(公开)/private(私有)/authenticated(需登录)';
COMMENT ON COLUMN file_metadata.source_url IS '从外部URL下载时记录原始地址';
COMMENT ON COLUMN file_metadata.thumbnail_path IS '缩略图MinIO路径';
COMMENT ON COLUMN file_metadata.metadata IS 'JSONB格式的额外元数据，如EXIF信息、自定义标签等';

-- ============================================================================
-- 第五部分：添加外键约束到 documents 表
-- ============================================================================

-- 如果 documents 表还没有这个外键，则添加
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'fk_documents_cover_image'
    ) THEN
        ALTER TABLE documents 
        ADD CONSTRAINT fk_documents_cover_image 
        FOREIGN KEY (cover_image_id) REFERENCES file_metadata(id) ON DELETE SET NULL;
    END IF;
END $$;

-- ============================================================================
-- 第六部分：创建触发器（自动更新 updated_at）
-- ============================================================================

-- 创建更新时间触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为 file_metadata 表添加触发器
DROP TRIGGER IF EXISTS trigger_file_metadata_updated_at ON file_metadata;
CREATE TRIGGER trigger_file_metadata_updated_at
    BEFORE UPDATE ON file_metadata
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- 执行完成
-- ============================================================================

-- 验证表是否创建成功
SELECT 
    'file_metadata 表创建成功' AS message,
    COUNT(*) AS column_count
FROM information_schema.columns 
WHERE table_name = 'file_metadata';

-- 验证 documents 表字段是否添加成功
SELECT 
    'documents 表扩展成功' AS message,
    COUNT(*) AS new_columns_count
FROM information_schema.columns 
WHERE table_name = 'documents' 
AND column_name IN ('file_size', 'import_mode', 'cover_image_id');

-- 显示所有索引
SELECT 
    tablename,
    indexname,
    indexdef
FROM pg_indexes 
WHERE tablename IN ('documents', 'file_metadata')
ORDER BY tablename, indexname;

-- ============================================================================
-- 使用说明
-- ============================================================================

/*
执行此脚本：

psql -U postgres -d doc_management -f 03-database-migration-minio.sql

预期结果：
  1. documents 表新增 8 个字段
  2. file_metadata 表创建成功
  3. 所有索引创建成功
  4. 外键约束添加成功
  5. 触发器创建成功

回滚脚本（如需要）：
  DROP TABLE IF EXISTS file_metadata CASCADE;
  ALTER TABLE documents 
    DROP COLUMN IF EXISTS file_size,
    DROP COLUMN IF EXISTS import_mode,
    DROP COLUMN IF EXISTS source_url,
    DROP COLUMN IF EXISTS imported_at,
    DROP COLUMN IF NOT EXISTS auto_sync,
    DROP COLUMN IF EXISTS last_synced_at,
    DROP COLUMN IF EXISTS cover_image_id,
    DROP COLUMN IF EXISTS cover_image_url;
*/

